package com.wdzg.member.generate.freemaker.generator.metadata;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.wdzg.member.generate.freemaker.generator.bean.Column;
import com.wdzg.member.generate.freemaker.generator.bean.StringValue;
import com.wdzg.member.generate.freemaker.generator.bean.XmlBean;

/**
 * @author administrator
 * @Time 2016-07-11 SQL API
 */
public class JDBCMsg {
	private static Logger log = Logger.getLogger(JDBCMsg.class);

	private static Connection conn;
	private static Statement statement;
	
	public static void main(String[] args) throws Exception {
		//开启数据库连接
		initConnection();
		
		// getAllDriverMsg();
		// getDBParameterMetaData();
		// demoDB();
		
		//ResultSetMetaData metaDate = getMetaDataFromTable();
		//displayMetaData(metaDate);
		
		String tableName = JDBCUtil.getTablename();
		String owner = JDBCUtil.getOwner();
		List<Map<String, String>> columns = getTablePdmCloumns(tableName, owner);
		for(Map<String, String> map : columns){
			System.out.println(map.get("Name") +"	"+ JDBCUtil.humpMarking(map.get("Code")) +"	"+ map.get("DataType") +"	"+ map.get("DisplaySize") 
					+"	"+ map.get("DataScale") +"	"+ convertColumn(map.get("DataType"), map.get("DisplaySize"), map.get("DataScale")) +"	"+ map.get("Nullable") +"	"+ map.get("Comment") +" "+ map.get("Primary") +"	"+ map.get("Mandatory"));
		}
		
		//关闭数据库连接
		disConnection();
	}
	
	public static XmlBean getBean(String tableName){
		JDBCUtil.setTablename(tableName);
		
		String[] arr = tableName.split("_");
		String modular = arr[1].toLowerCase();
		if("member".equals(modular)){
			modular = "wonder";
		}
		String modelName = "";
		for (int i = 2; i < arr.length; i++) {
			modelName = modelName + arr[i].toLowerCase();
		}
		
		XmlBean bean = new XmlBean();
		
		List<Column> columns = JDBCMsg.getColumns();
		bean.setColumns(columns);
		
		String projectPath = System.getProperty("user.home") +"\\git";
		projectPath = projectPath.replace("\\", "/") +"/member-admin/member-admin/";
		
		StringValue path = new StringValue();
		path.setJs(projectPath +"src/main/resources/static/js/"+ modular +"/"+ modelName);
		path.setJsp(projectPath +"src/main/resources/templates/"+ modular +"/"+ modelName);
		path.setVisitUrl(modular +"/"+ modelName);
		path.setCjava(projectPath +"src/main/java/com/wdzg/member/"+ modular +"/"+ modelName +"/controller");
		path.setSjava(projectPath +"src/main/java/com/wdzg/member/"+ modular +"/"+ modelName +"/service");
		//path.setDjava(projectPath +"src/main/java/com/wdzg/member/"+ modular +"/"+ modelName +"/dao");
		bean.setPath(path);
		
		bean.setTitle(modelName);
		bean.setModular(modular);
		bean.setModelName(modelName);
		bean.setTableName(tableName.toLowerCase());
		bean.setClassName(JDBCUtil.humpMarking(tableName));
		
		return bean;
	}
	
	public static List<Column> getColumns(){
		List<Column> list = new ArrayList<Column>();
		
		List<Map<String, String>> columns = getMetaData();
		for(Map<String, String> map : columns){
			Column col = new Column();
			col.setName(StringUtils.uncapitalize(JDBCUtil.humpMarking(map.get("Code"))));
			col.setType(convertColumn(map.get("DataType"), map.get("DisplaySize"), map.get("DataScale")));
			col.setLabel(map.get("Name"));
			col.setId("TRUE".equals(map.get("Primary")));
			col.setRequired("N".equals(map.get("Nullable")) ? "required" : "");
			
			String maxlength = "maxlength=\""+ map.get("DisplaySize") +"\"";
			if ("String".equals(col.getType())) {
				maxlength = "maxlength=\""+ Integer.parseInt(map.get("DisplaySize"))/2 +"\"";
			}
			col.setMaxLength(maxlength);
			
			//col.setSearch(true);
			col.setGrid(true);
			col.setSave(true);
			col.setUpdate(true);
			col.setView(true);
			
			list.add(col);
		}
		
		return list;
	}
	
	/**
	 * 获取元数据信息
	 * @since
	 */
	public static List<Map<String, String>> getMetaData() {
		List<Map<String, String>> columns = null;
		
		try {
			//开启数据库连接
			initConnection();
			
			String tableName = JDBCUtil.getTablename();
			String owner = JDBCUtil.getOwner();
			columns = getTablePdmCloumns(tableName.toUpperCase(), owner);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//关闭数据库连接
			try {
				disConnection();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return columns;
	}
	
	public static String convertColumn(String dataType, String displaySize, String dataScale){
		dataScale = (dataScale==null || dataScale.equals("")) ? "0" : dataScale;
		
		int size = Integer.parseInt(displaySize);
		int scale = Integer.parseInt(dataScale);
		
		String javaType = "String";
		
		if ("RAW".equals(dataType) || "LONGRAW".equals(dataType)) {
			javaType = "byte[]";
			
		} else if ("DATE".equals(dataType) || "TIMESTAMP".equals(dataType)) {
			javaType = "Date";
			
		} else if ("NUMBER".equals(dataType) && size<5 && scale==0) {
			javaType = "short";
			
		} else if ("NUMBER".equals(dataType) && size<5 && scale>0) {
			javaType = "float";
			
		} else if ("NUMBER".equals(dataType) && size<9 && scale==0) {
			javaType = "int";
			
		} else if ("NUMBER".equals(dataType) && size<9 && scale>0) {
			javaType = "double";
			
		} else if ("NUMBER".equals(dataType) && size>9) {
			javaType = "BigDecimal";
		}
		
		return javaType;
	}
	
	public static void initConnection() throws Exception {
		conn = JDBCUtil.getConnection();
		statement = conn.createStatement();
	}
	
	public static void disConnection() throws Exception {
		JDBCUtil.getConnection();
	}
	
	public static ResultSetMetaData getMetaDataFromTable() throws Exception {
		String tableName = JDBCUtil.getTablename();
		String sql = "SELECT * FROM "+ tableName.toUpperCase() +" WHERE 1 != 1";
		ResultSet rs = statement.executeQuery(sql);
		return rs.getMetaData();
	}
	
	public static void displayMetaData(ResultSetMetaData metaData) throws Exception {
		StringBuffer sb = new StringBuffer();
		
		for(int i=0; i<metaData.getColumnCount(); i++) {
			sb.append(metaData.getTableName(i + 1));
			sb.append(".");
			sb.append(JDBCUtil.humpMarking(metaData.getColumnName(i + 1)));
			sb.append("|");
			sb.append(metaData.getColumnType(i + 1));
			sb.append("|");
			sb.append(metaData.getColumnTypeName(i + 1));
			sb.append("|");
			sb.append(metaData.getColumnDisplaySize(i + 1));
			sb.append("|");
			sb.append(metaData.getScale(i + 1));
			sb.append("\n");
		}
		
		System.out.println(sb.toString());
	}
	
	/**
	 * 取一个数据库中所有表的信息
	 * 
	 * @throws SQLException
	 */
	public static void demoDB() throws SQLException {
		log.info("######  DatabaseMetaData关于数据库的整体综合信息====");
		java.sql.DatabaseMetaData dbmd = conn.getMetaData();

		log.info("数据库产品名: "+ dbmd.getDatabaseProductName());
		log.info("数据库是否支持事务: "+ dbmd.supportsTransactions());
		log.info("数据库产品的版本号:"+ dbmd.getDatabaseProductVersion());
		log.info("数据库的默认事务隔离级别:"+ dbmd.getDefaultTransactionIsolation());
		log.info("支持批量更新:"+ dbmd.supportsBatchUpdates());
		log.info("DBMS 的 URL:"+ dbmd.getURL());
		log.info("数据库的已知的用户名称:"+ dbmd.getUserName());
		log.info("数据库是否处于只读模式:"+ dbmd.isReadOnly());
		log.info("数据库是否支持为列提供别名:"+ dbmd.supportsColumnAliasing());
		log.info("是否支持指定 LIKE 转义子句:"+ dbmd.supportsLikeEscapeClause());
		log.info("是否为外连接提供受限制的支持:"+ dbmd.supportsLimitedOuterJoins());
		log.info("是否允许一次打开多个事务:"+ dbmd.supportsMultipleTransactions());
		log.info("是否支持 EXISTS 表达式中的子查询:"+ dbmd.supportsSubqueriesInExists());
		log.info("是否支持 IN 表达式中的子查询:"+ dbmd.supportsSubqueriesInIns());
		log.info("是否支持给定事务隔离级别:"+ dbmd.supportsTransactionIsolationLevel(1));
		log.info("此数据库是否支持事务:"+ dbmd.supportsTransactions());
		log.info("此数据库是否支持 SQL UNION:"+ dbmd.supportsUnion());
		log.info("此数据库是否支持 SQL UNION ALL:"+ dbmd.supportsUnionAll());
		log.info("此数据库是否为每个表使用一个文件:"+ dbmd.usesLocalFilePerTable());
		log.info("此数据库是否将表存储在本地文件中:"+ dbmd.usesLocalFiles());
		log.info("底层数据库的主版本号:"+ dbmd.getDatabaseMajorVersion());
		log.info("底层数据库的次版本号:"+ dbmd.getDatabaseMinorVersion());

		log.info("JDBC 驱动程序的主版本号:"+ dbmd.getJDBCMajorVersion());
		log.info("JDBC 驱动程序的次版本号:"+ dbmd.getJDBCMinorVersion());
		log.info("JDBC 驱动程序的名称:"+ dbmd.getDriverName());
		log.info("JDBC 驱动程序的 String 形式的版本号:"+ dbmd.getDriverVersion());

		log.info("可以在不带引号的标识符名称中使用的所有“额外”字符:"+ dbmd.getExtraNameCharacters());
		log.info("用于引用 SQL 标识符的字符串:"+ dbmd.getIdentifierQuoteString());
		log.info("允许用于类别名称的最大字符数:"+ dbmd.getMaxCatalogNameLength());
		log.info("允许用于列名称的最大字符数:"+ dbmd.getMaxColumnNameLength());
		log.info("允许在 GROUP BY 子句中使用的最大列数:"+ dbmd.getMaxColumnsInGroupBy());
		log.info("允许在 SELECT 列表中使用的最大列数:"+ dbmd.getMaxColumnsInSelect());
		log.info("允许在表中使用的最大列数:"+ dbmd.getMaxColumnsInTable());
		log.info("数据库的并发连接的可能最大数:"+ dbmd.getMaxConnections());
		log.info("允许用于游标名称的最大字符数:"+ dbmd.getMaxCursorNameLength());
		log.info("在同一时间内可处于开放状态的最大活动语句数:"+ dbmd.getMaxStatements());

		// 获取所有表 new String[]{"TABLE"}
		// String[] type = {"TABLE","VIEW"} null
		log.info("###### 获取表的信息");
		ResultSet tSet = dbmd.getTables(null, "%", "%", new String[] {"TABLE", "VIEW"});
		while (tSet.next()) {
			log.info(tSet.getRow() +"_表类别:"+ tSet.getString("TABLE_CAT")
					+ "_表模式:"+ tSet.getString("TABLE_SCHEM") +"_表名称:"
					+ tSet.getString("TABLE_NAME") +"_表类型:"
					+ tSet.getString("TABLE_TYPE")
			// +"\n_表的解释性注释:"+tSet.getString("REMARKS")+"_类型的类别:"+tSet.getString("TYPE_CAT")
			// +"\n_类型模式:"+tSet.getString("TYPE_SCHEM")+"_类型名称:"+tSet.getString("TYPE_NAME")
			// +"\n_有类型表的指定'identifier'列的名称:"+tSet.getString("SELF_REFERENCING_COL_NAME")
			// +"\n_指定在 SELF_REFERENCING_COL_NAME 中创建值的方式:"+tSet.getString("REF_GENERATION")
			);
			// 2_表类别:MANOR_表模式:PUBLIC_表名称:SYS_RESOURCE_表类型:TABLE
			String tableName = tSet.getString(3);
			String sql = "select * from "+ tableName;
			ResultSet rsSet = conn.createStatement().executeQuery(sql);
			ResultSetMetaData rsData = rsSet.getMetaData();
			for (int i = 1; i <= rsData.getColumnCount(); i++) {
				log.info("==列的信息:获取SQL语句的列名:"+ rsData.getColumnName(i) +"("
						+ rsData.getColumnLabel(i) +","
						+ rsData.getColumnType(i) +","
						+ rsData.getColumnClassName(i) +") 列宽"
						+ rsData.getPrecision(i) +" 大小写敏感"
						+ rsData.isCaseSensitive(i) +" isReadOnly:"
						+ rsData.isReadOnly(i));
				// ==列的信息:获取SQL语句的列名:LIMITLEVER(LIMITLEVER,5,java.lang.Short)
				// 列宽5 大小写敏感true isReadOnly:false
			}

		}
		tSet.close();

		log.info("###### 获取当前数据库所支持的SQL数据类型");
		ResultSet tableType = dbmd.getTypeInfo();
		while (tableType.next()) {
			log.info("数据类型名:"+ tableType.getString(1) +",短整型的数:"
					+ tableType.getString(2) +",整型的数:"
					+ tableType.getString(3) +",最小精度:"
					+ tableType.getString(14) +",最大精度:"
					+ tableType.getString(15));
			// 数据类型名:TIMESTAMP,短整型的数:93,整型的数:23,最小精度:0,最大精度:10
			// 数据类型名:VARCHAR,短整型的数:12,整型的数:2147483647,最小精度:0,最大精度:0
		}

		log.info("###### 表的主键列信息");
		ResultSet primaryKey = dbmd.getPrimaryKeys("MANOR", "PUBLIC", "SYS_ROLE_RES");
		while (primaryKey.next()) {
			log.info("表名:"+ primaryKey.getString("TABLE_NAME") +",列名:"
					+ primaryKey.getString("COLUMN_NAME") +" 主键名:"
					+ primaryKey.getString("PK_NAME"));
			// 表名:SYS_ROLE_RES,列名:SYS_RES_ID 主键名:CONSTRAINT_9
			// 表名:SYS_ROLE_RES,列名:SYS_ROLE_ID 主键名:CONSTRAINT_9
		}

		log.info("###### 表的外键列信息");
		ResultSet foreinKey = dbmd.getImportedKeys("MANOR", "PUBLIC", "SYS_ROLE_RES");
		while (foreinKey.next()) {
			log.info("主键名:"+ foreinKey.getString("PK_NAME") +",外键名:"
					+ foreinKey.getString("FKCOLUMN_NAME") +",主键表名:"
					+ foreinKey.getString("PKTABLE_NAME") +",外键表名:"
					+ foreinKey.getString("FKTABLE_NAME") +",外键列名:"
					+ foreinKey.getString("PKCOLUMN_NAME") +",外键序号:"
					+ foreinKey.getString("KEY_SEQ"));
			// 主键名:PRIMARY_KEY_95,外键名:SYS_RES_ID,主键表名:SYS_RESOURCE,外键表名:SYS_ROLE_RES,外键列名:ID,外键序号:1
			// 主键名:PRIMARY_KEY_A,外键名:SYS_ROLE_ID,主键表名:SYS_ROLE,外键表名:SYS_ROLE_RES,外键列名:ID,外键序号:1
		}

		log.info("###### 获取数据库中允许存在的表类型");
		ResultSet tableTypes = dbmd.getTableTypes();
		while (tableTypes.next()) {
			log.info("类型名:" + tableTypes.getString(1));
			/**
			 * H2 类型名:SYSTEM TABLE 类型名:TABLE 类型名:TABLE LINK 类型名:VIEW
			 */
		}

		// 此外还可以获取索引等的信息
		conn.close();
	}

	/**
	 * PreparedStatement 信息 ResultSetMetaData 信息
	 * 
	 * @throws SQLException
	 */
	public static void getDBParameterMetaData() throws SQLException {
		PreparedStatement pre = conn.prepareStatement("SELECT * FROM SYS_APPTYPE where id = ?");
		pre.setInt(1, 3);
		java.sql.ParameterMetaData pmd = pre.getParameterMetaData();
		log.info("参数的个数:"+ pmd.getParameterCount());
		log.info("获取指定参数的 SQL 类型:"+ pmd.getParameterType(1));
		log.info("culomn的参数类型:"+ pmd.getParameterTypeName(1));
		log.info("Java 类的完全限定名称:"+ pmd.getParameterClassName(1));
		log.info("获取指定参数的模式:"+ pmd.getParameterMode(1));
		log.info("获取指定参数的指定列大小:"+ pmd.getPrecision(1));
		log.info("获取指定参数的小数点右边的位数:"+ pmd.getScale(1));
		log.info("是否允许在指定参数中使用 null 值:"+ pmd.isNullable(1));
		log.info("指定参数的值是否可以是带符号的数字:"+ pmd.isSigned(1));

		// 获取结果集元数据
		ResultSet rs = pre.executeQuery();
		while (rs.next()) {
			log.info(rs.getString(1) +"___"+ rs.getString(2));
		}
		rs.close();
	}

	/**
	 * 获取所有Driver信息
	 */
	public static void getAllDriverMsg() {
		Enumeration<Driver> drivers = DriverManager.getDrivers();
		while (drivers.hasMoreElements()) {
			Driver d = drivers.nextElement();
			log.info(d.getClass().getName() +"_"+ d.getMajorVersion());
		}
	}
	
	/***
	 * 打印test
	 * 
	 * @throws SQLException
	 */
	public static List<Map<String, String>> getTablePdmCloumns(String Table, String Owner) throws SQLException {
		List<Map<String, String>> columns = new ArrayList<Map<String, String>>();

		try {
			String sql = "select "
					+ "         comments as \"Name\","
					+ "         a.column_name \"Code\","
					+ "         a.DATA_TYPE as \"DataType\","
					+ "         decode(a.data_precision, null, a.data_length, a.data_precision) as \"DisplaySize\","
					+ "         a.data_scale as \"DataScale\","
					+ "         a.nullable as \"Nullable\","
					+ "         b.comments as \"Comment\","
					+ "         decode(c.column_name,null,'FALSE','TRUE') as \"Primary\","
					+ "         decode(a.NULLABLE,'N','TRUE','Y','FALSE','') as \"Mandatory\","
					+ "         '' \"sequence\"" 
					+ "   from "
					+ "       all_tab_columns a, "
					+ "       all_col_comments b," 
					+ "       ("
					+ "        select a.constraint_name, a.column_name"
					+ "          from user_cons_columns a, user_constraints b"
					+ "         where a.constraint_name = b.constraint_name"
					+ "               and b.constraint_type = 'P'"
					+ "               and a.table_name = '"+ Table +"'"
					+ "       ) c" 
					+ "   where "
					+ "     a.Table_Name=b.table_Name "
					+ "     and a.column_name=b.column_name"
					+ "     and a.Table_Name='"+ Table +"'"
					+ "     and a.owner=b.owner " 
					+ "     and a.owner='"+ Owner +"'" 
					+ "     and a.COLUMN_NAME = c.column_name(+)"
					+ "  order by a.COLUMN_ID";
			//System.out.println(sql);
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				map.put("Name", rs.getString("Name"));
				map.put("Code", rs.getString("Code"));
				map.put("DataType", rs.getString("DataType"));
				map.put("DisplaySize", rs.getString("DisplaySize"));
				map.put("DataScale", rs.getString("DataScale"));
				map.put("Nullable", rs.getString("Nullable"));
				map.put("Comment", rs.getString("Comment"));
				map.put("Primary", rs.getString("Primary"));
				map.put("Mandatory", rs.getString("Mandatory"));
				columns.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return columns;
	}
}